﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using CCWin;
using SqlBuilder.Code;
using SqlBuilder.Model;
using System.IO;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace SqlBuilder
{
    public partial class MainForm : Skin_Metro
    {

        const string _jstemp = "function getcellvalue(){{{0}}} getcellvalue()";

        public MainForm()
        {
            InitializeComponent();
            //Loading.ShowLayer(this, null);
            this.TextBox_File.SkinTxt.Click += TextBox_File_Click;
            this.TextBox_Sql.SkinTxt.ScrollBars = ScrollBars.Vertical;
            this.TextBox_Sql.SkinTxt.KeyDown += SkinTxt_KeyDown;
        }

        private void SkinTxt_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Control&&e.KeyCode==Keys.A)
            {
                this.TextBox_Sql.SkinTxt.SelectAll();
            }
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            LoadData();
        }

        private void LoadData()
        {
            this.BeginInvoke(new Action(() =>
            {
                LoadTempLates(this.TextBox_SqlTemplateName.Text);
            }));
        }

        private void LoadTempLates(string name)
        {
            List<MSQLTemplate> list = SqlBuilderService.QueryTempLates(name);
            this.DataGridView_SqlTempLate.Rows.Clear();
            this.ComboBox_TempName.Items.Clear();
            if (list != null)
            {
                int index = 0;
                int comidnex = 0;
                foreach (MSQLTemplate item in list)
                {
                    index = this.DataGridView_SqlTempLate.Rows.Add();
                    this.DataGridView_SqlTempLate.Rows[index].Cells["Id"].Value = item.id;
                    this.DataGridView_SqlTempLate.Rows[index].Cells["TempName"].Value = item.name;
                    comidnex = this.ComboBox_TempName.Items.Add(new ComboBoxItem() { Text = item.name, Value = item.id });
                }
            }
        }

        private void Button_AddSqlTempLate_Click(object sender, EventArgs e)
        {
            EditSqlTempLate form = new EditSqlTempLate(string.Empty);
            form.completeEvent += Form_completeEvent;
            form.ShowDialog(this);

        }

        private void Form_completeEvent(bool obj)
        {
            LoadData();
        }

        private void Button_SeachTemplate_Click(object sender, EventArgs e)
        {
            LoadTempLates(this.TextBox_SqlTemplateName.Text);
        }

        private void DataGridView_SqlTempLate_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;
            }

            string id = this.DataGridView_SqlTempLate.Rows[e.RowIndex].Cells["Id"].Value.ToString();
            EditTempLate(id);
        }

        private void EditTempLate(string id)
        {
            EditSqlTempLate form = new EditSqlTempLate(id);
            form.completeEvent += Form_completeEvent;
            form.ShowDialog(this);
        }

        private int _selectRowIndex = 0;
        private void DataGridView_SqlTempLate_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;
            }

            this.DataGridView_SqlTempLate.Rows[e.RowIndex].Selected = true;
            _selectRowIndex = e.RowIndex;

        }

        private void 编辑ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            EditTempLate(this.DataGridView_SqlTempLate.Rows[_selectRowIndex].Cells["Id"].Value.ToString());
        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.BeginInvoke(new Action(() =>
            {
                string name = this.DataGridView_SqlTempLate.Rows[_selectRowIndex].Cells["TempName"].Value.ToString();
                string msg = string.Format("确定删除【{0}】？", name);
                if (MessageBoxEx.Show(msg, "温馨提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
                {
                    SqlBuilderService.DeleteTempLate(this.DataGridView_SqlTempLate.Rows[_selectRowIndex].Cells["Id"].Value.ToString());
                    LoadData();
                }
            }));

        }

        private void TextBox_File_Click(object sender, EventArgs e)
        {
            DialogResult result = this.openFileDialog1.ShowDialog(this);
            if (result == DialogResult.OK)
            {
                this.TextBox_File.Text = this.openFileDialog1.FileName;
            }
        }

        private void Button_Builde_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(this.TextBox_File.Text))
            {
                MessageBoxEx.Show("请选择Excel文件", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            ComboBoxItem comitem = this.ComboBox_TempName.SelectedItem as ComboBoxItem;
            if (comitem == null)
            {
                MessageBoxEx.Show("请选择模板", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            this.BeginInvoke(new Action(() =>
            {
                Loading.ShowLayer(this, new Action(() =>
                {
                    this.TextBox_Sql.Text = BulidSQL(comitem.Value.ToString(), this.TextBox_File.Text);

                }));
            }));
        }

        private string BulidSQL(string tid, string filename)
        {
            if (!File.Exists(filename))
            {
                MessageBoxEx.Show("不存在文件：" + filename, "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return string.Empty;
            }

            MTempAllInfo allinfo = SqlBuilderService.QueryAllInfo(tid);
            if (allinfo == null)
            {
                MessageBoxEx.Show("不存在此模板", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return string.Empty;
            }

            IWorkbook workbook = null;
            int dianindex = filename.LastIndexOf(".");
            string exname = filename.Substring(dianindex).ToLower();
            Stream stream = File.Open(filename, FileMode.Open);
            if (exname == ".xlsx")
            {
                workbook = new XSSFWorkbook(stream);
            }
            else if (exname == ".xls")
            {
                workbook = new HSSFWorkbook(stream);
            }
            else
            {
                MessageBoxEx.Show("文件格式错误：" + filename, "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return string.Empty;
            }


            return BulidSQL(allinfo, workbook);
        }

        private string BulidSQL(MTempAllInfo allinfo, IWorkbook workbook)
        {
            if (allinfo.SQLMap == null || allinfo.SQLMap.Count == 0)
            {
                return string.Empty;
            }

            ISheet sheet = workbook.GetSheetAt(0);
            IRow row = null;
            StringBuilder sb = new StringBuilder();
            string sql = string.Empty;
            object cellvalue = string.Empty;
            MRowInfo rowinfo = null;
            int nowcellindex = 0;
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                if (CheckIsOver(row, allinfo.SQLMap))
                {
                    break;
                }

                bool nowRowHasValue = false;
                foreach (var item in allinfo.SQLMap)
                {
                    if (row.GetCell(nowcellindex)!=null)
                    {
                        nowRowHasValue = true;
                        break;
                    }
                }

                if (!nowRowHasValue)
                {
                    continue;
                }

                sql = allinfo.SQLTemplate.template;
                foreach (var item in allinfo.SQLMap)
                {
                    nowcellindex = Convert.ToInt32(item.excelIndex);
                    rowinfo = BuildeRowInfo(row, nowcellindex, i);



                    cellvalue = row.GetCell(nowcellindex).ToString();
                    if (!string.IsNullOrEmpty(item.script))
                    {
                        cellvalue = ScriptUtil.Execute<string>(string.Format(_jstemp, item.script), new Dictionary<string, object>() { { "RowInfo", rowinfo } });
                    }
                    sql = sql.Replace(item.template, string.Format("'{0}'", cellvalue));
                }

                sb.Append(sql + ";\r\n");
            }

            return sb.ToString();
        }

        private MRowInfo BuildeRowInfo(IRow row, int nowcellindex, int nowrowindex)
        {
            MRowInfo info = new MRowInfo()
            {
                NowCellIndex = nowcellindex,
                NowRowIndex = nowrowindex,
                Cells = new List<string>()
            };

            info.NowCellValue = row.GetCell(nowcellindex).ToString();
            foreach (var item in row.Cells)
            {
                info.Cells.Add(item.ToString());
            }

            return info;
        }

        private bool CheckIsOver(IRow row, List<MSQLMap> maps)
        {
            //bool isover = true;
            //foreach (var item in maps)
            //{


            //    if (row.GetCell(Convert.ToInt32(item.excelIndex))!=null&&!string.IsNullOrEmpty(row.GetCell(Convert.ToInt32(item.excelIndex)).ToString()))
            //    {
            //        isover = false;
            //        break;
            //    }
            //}
            bool isover = row.Cells == null || row.Cells.Count == 0 || row.Cells.Count(x => !string.IsNullOrEmpty(x.ToString())) == 0;
            return isover;
        }
    }
}
